﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using System.IO.Compression;
using System.Xml;
using System.Xml.Serialization;

namespace DACExplorer
{
    public partial class DacExplorer : Form
    {
        public string FilePath = "";
        public string _PostScriptContent = "";
        public string _PreScriptContent = "";
        public Stream XMLModel = new MemoryStream();

        public DataSchemaModel _I_DBModel;


        public DacExplorer()
        {
            InitializeComponent();
        }
        public DacExplorer(string filename)
        {
            FilePath = filename;
            InitializeComponent();
        }
        private void DacExplorer_Load(object sender, EventArgs e)
        {
            this.Text = string.Format("{0} -({1})", Application.ProductName, Application.ProductVersion.ToString());
            if (!string.IsNullOrEmpty(FilePath)) OpenFile();
        }

        private void toolStripButton1_Click(object sender, EventArgs e)
        {
            OpenFile();
            toolStripButton1.Enabled = false;
        }

        void OpenFile()
        {
            var openFileDialog1 = new OpenFileDialog();
            if (string.IsNullOrEmpty(FilePath))
            {
                openFileDialog1.Filter = @"databasemodelfile (.dacpac;*.bacpac)|*.dacpac;*.bacpac|databasemodelfile (.bacpac)|*.bacpac|model XML file (model.xml)|model.xml";
                openFileDialog1.FilterIndex = 1;
                openFileDialog1.Multiselect = false;
                if (openFileDialog1.ShowDialog() == DialogResult.OK)
                {
                    FilePath = openFileDialog1.FileName;
                }
            }

            if ((!string.IsNullOrEmpty(FilePath)))
            {
                
                //Dacpac
                {
                    try
                    {
                        using (var file = new FileStream(FilePath, FileMode.Open))
                        {
                            using (var zip = new System.IO.Compression.ZipArchive(file, ZipArchiveMode.Read))
                            {
                                
                                Stream XMLDacMetadata = new MemoryStream();
                                string DatabaseName = "";
                                string DatabaseVersion = "";
                                string DatabaseDescription = "";
                                bool IsDataInclude = false;
                                bool IsDacpac = false;
                                bool PreScripts = false;
                                bool PostScripts = false;
                                bool RefactorLog = false;
                                List<string> DataTableIncludes = new List<string>();

                                if (FilePath.ToLower().Contains(".dacpac"))
                                    IsDacpac = true;

                                foreach (ZipArchiveEntry entry in zip.Entries)
                                {
                                    if (entry.FullName.ToLower().Contains(@"data/"))
                                    {
                                        IsDataInclude = true;
                                        string tmpstring = entry.FullName.Remove(0, entry.FullName.IndexOf('/') + 1);
                                        tmpstring = tmpstring.Remove(tmpstring.LastIndexOf('/'));
                                        tmpstring = '[' + tmpstring.Remove(tmpstring.IndexOf('.')) + "].[" + tmpstring.Remove(0, tmpstring.IndexOf('.') + 1) + ']';

                                        DataTableIncludes.Add(tmpstring);
                                    }
                                    if (entry.Name == "model.xml")
                                    {
                                        XMLModel = entry.Open();
                                    }
                                    if (entry.Name == "DacMetadata.xml")
                                    {
                                        XMLDacMetadata = entry.Open();
                                        ExtractModelDetailes(XMLDacMetadata, out DatabaseName, out DatabaseVersion, out DatabaseDescription);
                                    }

                                    if (entry.Name == "postdeploy.sql")
                                    {
                                        PostScripts = true;
                                        using (var tt = new StreamReader(entry.Open()))
                                        {
                                            _PostScriptContent = tt.ReadToEnd();
                                        }
                                    }

                                    if (entry.Name == "predeploy.sql")
                                    {
                                        PreScripts = true;
                                        using (var tt = new StreamReader(entry.Open()))
                                        {
                                            _PreScriptContent = tt.ReadToEnd();
                                        }
                                    }

                                    if (entry.Name == "refactor.xml")
                                    {
                                        RefactorLog = true;
                                    }
                                }
                                List<string> b = new List<string>();
                                b.AddRange(DataTableIncludes.Distinct());

                                OpenModelFile(XMLModel, DatabaseName, DatabaseVersion, DatabaseDescription, IsDacpac, IsDataInclude, b,PreScripts,PostScripts,RefactorLog);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(string.Format("Failed to open file: {0} \nError: {1} ", openFileDialog1.FileName.ToLower(), ex.Message));
                    }
                }

            }
        }

        void OpenModelFile(Stream input, string DatabaseName, string DatabaseVersion, string DatabaseDescription, bool IsDacpac, bool IsDataIncludes, List<string> DataTableIncludes,bool PreScript , bool PostScript,bool refactorlog)
        {
            using (XmlReader xmlReader= XmlReader.Create(input))
            {
                var serializer = new XmlSerializer(typeof(DataSchemaModel));
                _I_DBModel = serializer.Deserialize(xmlReader) as DataSchemaModel;

                var ModelLevel = new TreeNode(string.Format("{0} - ({1})", DatabaseName, DatabaseVersion), 0, 0);
                ModelLevel.ToolTipText = DatabaseDescription;
                ModelLevel.Expand();
                {
                    var databaseLevel = new TreeNode(string.Format("{0}", DatabaseName), 16, 16);
                    databaseLevel.ToolTipText = DatabaseDescription;
                    databaseLevel.Expand();
                    {
                        var databaseProperties = new TreeNode("Database Properties", 1, 1);
                        {

                        }
                        databaseLevel.Nodes.Add(databaseProperties);

                        var tables = new TreeNode("Tables", 2, 2);
                        {
                            int i = 0;
                            if (_I_DBModel != null)
                                foreach (var element in _I_DBModel.Model)
                                {
                                    if (element.Type == "SqlTable")
                                    {
                                        i++;
                                        var elementNode = new TreeNode(element.Name, 2, 2);
                                        {
                                            //Table's objects
                                            var Columns = new TreeNode("Columns", 1, 1);
                                            {
                                                foreach (var colname in element.Items)
                                                {
                                                    if ((colname.GetType() == typeof(DataSchemaModelElementRelationship)))
                                                    {
                                                        if ((colname.GetType().GetProperty("Name") != null) && (typeof(DataSchemaModelElementRelationship).GetProperty("Name").GetValue(colname).ToString() == "Columns"))
                                                        {
                                                            var ll = (DataSchemaModelElementRelationship)colname;
                                                            foreach (DataSchemaModelElementRelationshipEntry tt in ll.Entry)
                                                            {


                                                                try
                                                                {
                                                                    string colmnProperties = "";
                                                                    string colType = "";
                                                                    if (tt.Element.Type.ToString() == "SqlComputedColumn")
                                                                    {
                                                                        colType = "ComputedColumn";
                                                                    }
                                                                    else
                                                                    {
                                                                        colType =
                                                                            tt.Element.Relationship.Entry[0].Element
                                                                                                            .Relationship[0]
                                                                                .Entry.References.Name.ToString()
                                                                                .Replace("[", "")
                                                                                .Replace("]", "");
                                                                    }
                                                                    string colName = "";
                                                                    colName = tt.Element.Name.Remove(0, tt.Element.Name.LastIndexOf(".") + 1);

                                                                    if (tt.Element.Property != null)
                                                                    {
                                                                        foreach (DataSchemaModelElementRelationshipEntryElementProperty prop in tt.Element.Property)
                                                                        {
                                                                            if ((prop.Value1 != null))
                                                                            {
                                                                                if (prop.Value1.ToString() == "True")
                                                                                    colmnProperties += prop.Name.Remove(0, 2) + ";";
                                                                            }
                                                                            else
                                                                            {
                                                                                colmnProperties += prop.Value;
                                                                            }
                                                                        }
                                                                    }

                                                                    string text = string.Format("{0} - {1} ({2})", colName, colType, colmnProperties);
                                                                    var col = new TreeNode(text, 1, 1);

                                                                    Columns.Nodes.Add(col);
                                                                }
                                                                catch (Exception ex)
                                                                {
                                                                    MessageBox.Show("Error while generating Columns ");
                                                                }
                                                            }
                                                        }
                                                    }
                                                }
                                            }
                                            elementNode.Nodes.Add(Columns);

                                            //Keys
                                            var Keys = new TreeNode("Keys", 1, 1);
                                            {
                                            }
                                            elementNode.Nodes.Add(Keys);

                                            //Constraints
                                            var constraints = new TreeNode("Constraints", 1, 1);
                                            {
                                                int j = 0;
                                                foreach (var indexElement in _I_DBModel.Model)
                                                {
                                                    if (indexElement.Type == "SqlCheckConstraint")
                                                    {
                                                        //if indexname contains table name
                                                        foreach (var dataTableInclude in indexElement.Items)
                                                        {
                                                            if (dataTableInclude.GetType() == typeof(DataSchemaModelElementRelationship))
                                                            {
                                                                if (
                                                                    dataTableInclude.GetType()
                                                                                    .GetProperty("Name")
                                                                                    .GetValue(dataTableInclude)
                                                                                    .ToString() == "DefiningTable")
                                                                {
                                                                    var elementName = (DataSchemaModelElementRelationshipEntry[])dataTableInclude.GetType().GetProperty("Entry").GetValue(dataTableInclude);

                                                                    if (elementName[0].References.Name == element.Name)
                                                                    {
                                                                        j++;
                                                                        var indexeElementNode =
                                                                            new TreeNode(indexElement.Name,
                                                                                         2, 2);
                                                                        constraints.Nodes.Add(indexeElementNode);
                                                                    }
                                                                }
                                                            }
                                                        }
                                                    }
                                                }
                                                constraints.Text += string.Format(" ({0})", arg0: j.ToString());
                                            }
                                            elementNode.Nodes.Add(constraints);

                                            //Triggers
                                            var triggers = new TreeNode("Dml Triggers", 1, 1);
                                            {
                                                int j = 0;
                                                foreach (var triggersElement in _I_DBModel.Model)
                                                {
                                                    if (triggersElement.Type == "SqlDmlTrigger")
                                                    {
                                                        //if indexname contains table name
                                                        foreach (var dataTableInclude in triggersElement.Items)
                                                        {
                                                            if (dataTableInclude.GetType() == typeof(DataSchemaModelElementRelationship))
                                                            {
                                                                if (
                                                                    dataTableInclude.GetType()
                                                                                    .GetProperty("Name")
                                                                                    .GetValue(dataTableInclude)
                                                                                    .ToString() == "Parent")
                                                                {
                                                                    var elementName = (DataSchemaModelElementRelationshipEntry[])dataTableInclude.GetType().GetProperty("Entry").GetValue(dataTableInclude);

                                                                    if (elementName[0].References.Name == element.Name)
                                                                    {
                                                                        j++;
                                                                        var triggersElementNode =
                                                                            new TreeNode(triggersElement.Name,
                                                                                         8, 8);
                                                                        triggers.Nodes.Add(triggersElementNode);
                                                                    }
                                                                }
                                                            }
                                                        }
                                                    }
                                                }
                                                triggers.Text += string.Format(" ({0})", arg0: j.ToString());
                                            }
                                            elementNode.Nodes.Add(triggers);

                                            //Indexes
                                            var indexes = new TreeNode("Indexes", 1, 1);
                                            {
                                                int j = 0;
                                                foreach (var indexElement in _I_DBModel.Model)
                                                {
                                                    if (indexElement.Type == "SqlIndex")
                                                    {
                                                        //if indexname contains table name
                                                        if (indexElement.Name.Contains(element.Name))
                                                        {
                                                            j++;
                                                            var indexeElementNode = new TreeNode(indexElement.Name, 15, 15);
                                                            indexes.Nodes.Add(indexeElementNode);
                                                        }
                                                    }
                                                }
                                                indexes.Text += string.Format(" ({0})", arg0: j.ToString());
                                            }
                                            elementNode.Nodes.Add(indexes);

                                            //Statistics
                                            var Statistics = new TreeNode("Statistics", 1, 1);
                                            {
                                            }
                                            elementNode.Nodes.Add(Statistics);
                                        }
                                        foreach (string datatablename in DataTableIncludes)
                                        {
                                            if (datatablename == element.Name)
                                            {
                                                elementNode.ForeColor = Color.Blue;
                                                elementNode.Text += string.Format("    *Data is includes");
                                            }
                                        }
                                        tables.Nodes.Add(elementNode);
                                    }
                                }
                            tables.Text += string.Format(" ({0})", arg0: i.ToString());
                        }
                        databaseLevel.Nodes.Add(tables);

                        var views = new TreeNode("Views", 3, 3);
                        {
                            int i = 0;
                            if (_I_DBModel != null)
                                foreach (var element in _I_DBModel.Model)
                                {
                                    if (element.Type == "SqlView")
                                    {
                                        i++;
                                        var elementNode = new TreeNode(element.Name, 2, 2);
                                        views.Nodes.Add(elementNode);
                                    }
                                }
                            views.Text += string.Format(" ({0})", arg0: i.ToString());
                        }
                        databaseLevel.Nodes.Add(views);

                        var synonm = new TreeNode("Synonm", 4, 4);
                        {
                            int i = 0;
                            if (_I_DBModel != null)
                                foreach (var element in _I_DBModel.Model)
                                {
                                    if (element.Type == "SqlSynonym")
                                    {
                                        i++;
                                        var elementNode = new TreeNode(element.Name, 2, 2);
                                        synonm.Nodes.Add(elementNode);
                                    }
                                }
                            synonm.Text += string.Format(" ({0})", i.ToString());
                        }
                        databaseLevel.Nodes.Add(synonm);

                        var programability = new TreeNode("Programability", 1, 1);
                        {
                            //SPs
                            var storesProcedures = new TreeNode("Stores Procedures", 5, 5);
                            {
                                int i = 0;
                                if (_I_DBModel != null)
                                    foreach (var element in _I_DBModel.Model)
                                    {
                                        if (element.Type == "SqlProcedure")
                                        {
                                            i++;
                                            var elementNode = new TreeNode(element.Name, 5, 5);
                                            storesProcedures.Nodes.Add(elementNode);
                                        }
                                    }
                                storesProcedures.Text += string.Format(" ({0})", arg0: i.ToString());
                            }
                            programability.Nodes.Add(storesProcedures);
                            //Functions
                            var functions = new TreeNode("Functions", 1, 1);
                            {
                                //ScalarFunction
                                var scalarFunction = new TreeNode("Scalar Functions", 7, 7);
                                {
                                    int i = 0;
                                    if (_I_DBModel != null)
                                        foreach (var element in _I_DBModel.Model)
                                        {
                                            if (element.Type == "SqlScalarFunction")
                                            {
                                                i++;
                                                var elementNode = new TreeNode(element.Name, 7, 7);
                                                scalarFunction.Nodes.Add(elementNode);
                                            }
                                        }
                                    scalarFunction.Text += string.Format(" ({0})", arg0: i.ToString());
                                }
                                functions.Nodes.Add(scalarFunction);

                                //SqlMultiStatementTableValuedFunction
                                var sqlMultiStatementTableValuedFunction = new TreeNode("Table Valued Functions", 6, 6);
                                {
                                    int i = 0;
                                    if (_I_DBModel != null)
                                        foreach (var element in _I_DBModel.Model)
                                        {
                                            if (element.Type == "SqlMultiStatementTableValuedFunction")
                                            {
                                                i++;
                                                var elementNode = new TreeNode(element.Name, 6, 6);
                                                sqlMultiStatementTableValuedFunction.Nodes.Add(elementNode);
                                            }
                                        }
                                    sqlMultiStatementTableValuedFunction.Text += string.Format(" ({0})", arg0: i.ToString());
                                }
                                functions.Nodes.Add(sqlMultiStatementTableValuedFunction);

                            }
                            programability.Nodes.Add(functions);
                            //Database Triggers
                            var databaseTriggers = new TreeNode("Database Triggers", 8, 8);
                            {
                                int i = 0;
                                if (_I_DBModel != null)
                                    foreach (var element in _I_DBModel.Model)
                                    {
                                        if (element.Type == "SqlDatabaseDdlTrigger")
                                        {
                                            i++;
                                            var elementNode = new TreeNode(element.Name, 8, 8);
                                            databaseTriggers.Nodes.Add(elementNode);
                                        }
                                    }
                                databaseTriggers.Text += string.Format(" ({0})", arg0: i.ToString());
                            }
                            programability.Nodes.Add(databaseTriggers);
                            //assemblyes
                            var assemblyes = new TreeNode("Assemblyes", 9, 9);
                            {
                                int i = 0;
                                if (_I_DBModel != null)
                                    foreach (var element in _I_DBModel.Model)
                                    {
                                        if (element.Type == "SqlAssembly")
                                        {
                                            i++;
                                            var elementNode = new TreeNode(element.Name, 9, 9);
                                            assemblyes.Nodes.Add(elementNode);
                                        }
                                    }
                                assemblyes.Text += string.Format(" ({0})", arg0: i.ToString());
                            }
                            programability.Nodes.Add(assemblyes);
                            //Types
                            var types = new TreeNode("Types", 1, 1);
                            {
                                //SqlUserDefinedDataType
                                var sqlUserDefinedDataType = new TreeNode("User Defined Data Type", 10, 10);
                                {
                                    int i = 0;
                                    if (_I_DBModel != null)
                                        foreach (var element in _I_DBModel.Model)
                                        {
                                            if (element.Type == "SqlUserDefinedDataType")
                                            {
                                                i++;
                                                var elementNode = new TreeNode(element.Name, 10, 10);
                                                sqlUserDefinedDataType.Nodes.Add(elementNode);
                                            }
                                        }
                                    sqlUserDefinedDataType.Text += string.Format(" ({0})", arg0: i.ToString());
                                }
                                types.Nodes.Add(sqlUserDefinedDataType);
                                //SqlTableType
                                var sqlTableType = new TreeNode("Table Types", 2, 2);
                                {
                                    int i = 0;
                                    if (_I_DBModel != null)
                                        foreach (var element in _I_DBModel.Model)
                                        {
                                            if (element.Type == "SqlTableType")
                                            {
                                                i++;
                                                var elementNode = new TreeNode(element.Name, 2, 2);
                                                sqlTableType.Nodes.Add(elementNode);
                                            }
                                        }
                                    sqlTableType.Text += string.Format(" ({0})", arg0: i.ToString());
                                }
                                types.Nodes.Add(sqlTableType);
                            }
                            programability.Nodes.Add(types);

                            //Sequences
                            var sequences = new TreeNode("Sequences", 11, 11);
                            {
                                int i = 0;
                                if (_I_DBModel != null)
                                    foreach (var element in _I_DBModel.Model)
                                    {
                                        if (element.Type == "SqlSequence")
                                        {
                                            i++;
                                            var elementNode = new TreeNode(element.Name, 11, 11);
                                            sequences.Nodes.Add(elementNode);
                                        }
                                    }
                                sequences.Text += string.Format(" ({0})", arg0: i.ToString());
                            }
                            programability.Nodes.Add(sequences);

                        }
                        databaseLevel.Nodes.Add(programability);

                        var serviceBroker = new TreeNode("Service Broker", 1, 1);
                        {

                        }
                        databaseLevel.Nodes.Add(serviceBroker);

                        var storage = new TreeNode("Storage", 1, 1);
                        {
                            //sqlFullTextCatalog
                            var sqlFullTextCatalog = new TreeNode("Full Text Catalogs", 14, 14);
                            {
                                int i = 0;
                                if (_I_DBModel != null)
                                    foreach (var element in _I_DBModel.Model)
                                    {
                                        if (element.Type == "SqlFullTextCatalog")
                                        {
                                            i++;
                                            var elementNode = new TreeNode(element.Name, 14, 14);
                                            sqlFullTextCatalog.Nodes.Add(elementNode);
                                        }
                                    }
                                sqlFullTextCatalog.Text += string.Format(" ({0})", arg0: i.ToString());
                            }
                            storage.Nodes.Add(sqlFullTextCatalog);

                            //SqlPartitionScheme
                            var sqlPartitionScheme = new TreeNode("Partition Scheme", 14, 14);
                            {
                                int i = 0;
                                if (_I_DBModel != null)
                                    foreach (var element in _I_DBModel.Model)
                                    {
                                        if (element.Type == "SqlPartitionScheme")
                                        {
                                            i++;
                                            var elementNode = new TreeNode(element.Name, 14, 14);
                                            sqlPartitionScheme.Nodes.Add(elementNode);
                                        }
                                    }
                                sqlPartitionScheme.Text += string.Format(" ({0})", arg0: i.ToString());
                            }
                            storage.Nodes.Add(sqlPartitionScheme);

                            //sqlPartitionFunction
                            var sqlPartitionFunction = new TreeNode("Partition Function", 14, 14);
                            {
                                int i = 0;
                                if (_I_DBModel != null)
                                    foreach (var element in _I_DBModel.Model)
                                    {
                                        if (element.Type == "SqlPartitionFunction")
                                        {
                                            i++;
                                            var elementNode = new TreeNode(element.Name, 14, 14);
                                            sqlPartitionFunction.Nodes.Add(elementNode);
                                        }
                                    }
                                sqlPartitionFunction.Text += string.Format(" ({0})", arg0: i.ToString());
                            }
                            storage.Nodes.Add(sqlPartitionFunction);

                            //sqlPartitionFunction
                            var sqlFullTextStopList = new TreeNode("Full Text StopList", 14, 14);
                            {
                                int i = 0;
                                if (_I_DBModel != null)
                                    foreach (var element in _I_DBModel.Model)
                                    {
                                        if (element.Type == "SqlFullTextStopList")
                                        {
                                            i++;
                                            var elementNode = new TreeNode(element.Name, 14, 14);
                                            sqlFullTextStopList.Nodes.Add(elementNode);
                                        }
                                    }
                                sqlPartitionFunction.Text += string.Format(" ({0})", arg0: i.ToString());
                            }
                            storage.Nodes.Add(sqlFullTextStopList);
                        }
                        databaseLevel.Nodes.Add(storage);

                        var security = new TreeNode("Security", 1, 1);
                        {
                            //Users
                            var sqlUser = new TreeNode("Database Users", 12, 12);
                            {
                                int i = 0;
                                if (_I_DBModel != null)
                                    foreach (var element in _I_DBModel.Model)
                                    {
                                        if (element.Type == "SqlUser")
                                        {
                                            i++;
                                            var elementNode = new TreeNode(element.Name, 12, 12);
                                            sqlUser.Nodes.Add(elementNode);
                                        }
                                    }
                                sqlUser.Text += string.Format(" ({0})", arg0: i.ToString());
                            }
                            security.Nodes.Add(sqlUser);
                            //Roles
                            var roles = new TreeNode("Roles", 1, 1);
                            {
                                //Database Roles
                                var sqlRole = new TreeNode("Database Roles", 13, 13);
                                {
                                    int i = 0;
                                    if (_I_DBModel != null)
                                        foreach (var element in _I_DBModel.Model)
                                        {
                                            if (element.Type == "SqlRole")
                                            {
                                                i++;
                                                var elementNode = new TreeNode(element.Name, 13, 13);
                                                sqlRole.Nodes.Add(elementNode);
                                            }
                                        }
                                    sqlRole.Text += string.Format(" ({0})", arg0: i.ToString());
                                }
                                roles.Nodes.Add(sqlRole);

                                //Application Role
                                var sqlAppRole = new TreeNode("Application Roles", 13, 13);
                                {
                                    int i = 0;
                                    if (_I_DBModel != null)
                                        foreach (var element in _I_DBModel.Model)
                                        {
                                            if (element.Type == "SqlApplicationRole")
                                            {
                                                i++;
                                                var elementNode = new TreeNode(element.Name, 13, 13);
                                                sqlAppRole.Nodes.Add(elementNode);
                                            }
                                        }
                                    sqlAppRole.Text += string.Format(" ({0})", arg0: i.ToString());
                                }
                                roles.Nodes.Add(sqlAppRole);
                            }
                            security.Nodes.Add(roles);


                            //Schema
                            var sqlSchema = new TreeNode("Schemas", 13, 13);
                            {
                                int i = 0;
                                if (_I_DBModel != null)
                                    foreach (var element in _I_DBModel.Model)
                                    {
                                        if (element.Type == "SqlSchema")
                                        {
                                            i++;
                                            var elementNode = new TreeNode(element.Name, 0, 0);
                                            sqlSchema.Nodes.Add(elementNode);
                                        }
                                    }
                                sqlSchema.Text += string.Format(" ({0})", arg0: i.ToString());
                            }
                            security.Nodes.Add(sqlSchema);
                        }
                        databaseLevel.Nodes.Add(security);
                    }
                    ModelLevel.Nodes.Add(databaseLevel);
                }
                //Server Level
                {
                    var ServerLevel = new TreeNode("Server related Objects", 17, 17);
                    {

                        {
                            var ServerSecurityLevel = new TreeNode("Security", 1, 1);

                            //Server Logins
                            {
                                var ServerLoginsLevel = new TreeNode("Server Logins", 12, 12);
                                {
                                    int i = 0;
                                    if (_I_DBModel != null)
                                        foreach (var element in _I_DBModel.Model)
                                        {
                                            if (element.Type == "SqlLogin")
                                            {
                                                i++;
                                                var elementNode = new TreeNode(element.Name, 12, 12);
                                                ServerLoginsLevel.Nodes.Add(elementNode);
                                            }
                                        }
                                    ServerLoginsLevel.Text += string.Format(" ({0})", arg0: i.ToString());
                                }
                                ServerSecurityLevel.Nodes.Add(ServerLoginsLevel);
                            }
                            //Server Roles
                            {
                                var ServerRoleLevel = new TreeNode("Server Roles", 13, 13);
                                {
                                    int i = 0;
                                    if (_I_DBModel != null)
                                        foreach (var element in _I_DBModel.Model)
                                        {
                                            if (element.Type == "SqlUserDefinedServerRole")
                                            {
                                                i++;
                                                var elementNode = new TreeNode(element.Name, 13, 13);
                                                ServerRoleLevel.Nodes.Add(elementNode);
                                            }
                                        }
                                    ServerRoleLevel.Text += string.Format(" ({0})", arg0: i.ToString());
                                }
                                ServerSecurityLevel.Nodes.Add(ServerRoleLevel);
                            }
                            //Server Credentials
                            {
                                var ServerRoleLevel = new TreeNode("Credentials", 12, 12);
                                {
                                    int i = 0;
                                    if (_I_DBModel != null)
                                        foreach (var element in _I_DBModel.Model)
                                        {
                                            if (element.Type == "SqlCredential")
                                            {
                                                i++;
                                                var elementNode = new TreeNode(element.Name, 0, 0);
                                                ServerRoleLevel.Nodes.Add(elementNode);
                                            }
                                        }
                                    ServerRoleLevel.Text += string.Format(" ({0})", arg0: i.ToString());
                                }
                                ServerSecurityLevel.Nodes.Add(ServerRoleLevel);
                            }
                            //Server Audits
                            {
                                var SqlServerAuditLevel = new TreeNode("Audits", 1, 1);
                                {
                                    int i = 0;
                                    if (_I_DBModel != null)
                                        foreach (var element in _I_DBModel.Model)
                                        {
                                            if (element.Type == "SqlServerAudit")
                                            {
                                                i++;
                                                var elementNode = new TreeNode(element.Name, 0, 0);
                                                SqlServerAuditLevel.Nodes.Add(elementNode);
                                            }
                                        }
                                    SqlServerAuditLevel.Text += string.Format(" ({0})", arg0: i.ToString());
                                }
                                ServerSecurityLevel.Nodes.Add(SqlServerAuditLevel);
                            }
                            //Server Audits
                            {
                                var SqlServerAuditSpecificationLevel = new TreeNode("Audit Specification", 1, 1);
                                {
                                    int i = 0;
                                    if (_I_DBModel != null)
                                        foreach (var element in _I_DBModel.Model)
                                        {
                                            if (element.Type == "SqlServerAuditSpecification")
                                            {
                                                i++;
                                                var elementNode = new TreeNode(element.Name, 0, 0);
                                                SqlServerAuditSpecificationLevel.Nodes.Add(elementNode);
                                            }
                                        }
                                    SqlServerAuditSpecificationLevel.Text += string.Format(" ({0})", arg0: i.ToString());
                                }
                                ServerSecurityLevel.Nodes.Add(SqlServerAuditSpecificationLevel);
                            }
                            ////////////
                            ServerLevel.Nodes.Add(ServerSecurityLevel);
                        }

                        //Server Objects
                        {
                            var ServerObjectsLevel = new TreeNode("Server Objects", 1, 1);
                            //////////////////////////////////////////////////////////


                            //Server LinkedServers
                            {
                                var ServerLinkedServersLevel = new TreeNode("Linked Servers", 1, 1);
                                {
                                    int i = 0;
                                    if (_I_DBModel != null)
                                        foreach (var element in _I_DBModel.Model)
                                        {
                                            if (element.Type == "SqlLinkedServer")
                                            {
                                                i++;
                                                var elementNode = new TreeNode(element.Name, 0, 0);
                                                ServerLinkedServersLevel.Nodes.Add(elementNode);
                                            }
                                        }
                                    ServerLinkedServersLevel.Text += string.Format(" ({0})", arg0: i.ToString());
                                }
                                ServerObjectsLevel.Nodes.Add(ServerLinkedServersLevel);
                            }
                            //Server Triggers
                            {
                                var ServerTriggerLevel = new TreeNode("Server Triggers", 1, 1);
                                {
                                    int i = 0;
                                    if (_I_DBModel != null)
                                        foreach (var element in _I_DBModel.Model)
                                        {
                                            if (element.Type == "SqlServerDdlTrigger")
                                            {
                                                i++;
                                                var elementNode = new TreeNode(element.Name, 0, 0);
                                                ServerTriggerLevel.Nodes.Add(elementNode);
                                            }
                                        }
                                    ServerTriggerLevel.Text += string.Format(" ({0})", arg0: i.ToString());
                                }
                                ServerObjectsLevel.Nodes.Add(ServerTriggerLevel);
                            }
                            ////////////////////////////////////////////////////////
                            ServerLevel.Nodes.Add(ServerObjectsLevel);
                        }

                        //Managment
                        {
                            var ServerManagmentLevel = new TreeNode("Managment", 1, 1);
                            ////////////////////
                            //Server ErrorMessages
                            {
                                var SqlErrorMessageLevel = new TreeNode("User defined Error messages", 1, 1);
                                {
                                    int i = 0;
                                    if (_I_DBModel != null)
                                        foreach (var element in _I_DBModel.Model)
                                        {
                                            if (element.Type == "SqlErrorMessage")
                                            {
                                                i++;
                                                var elementNode = new TreeNode(element.Name, 0, 0);
                                                SqlErrorMessageLevel.Nodes.Add(elementNode);
                                            }
                                        }
                                    SqlErrorMessageLevel.Text += string.Format(" ({0})", arg0: i.ToString());
                                }
                                ServerManagmentLevel.Nodes.Add(SqlErrorMessageLevel);
                            }

                            //Server Event Traces
                            {
                                var SqlErrorMessageLevel = new TreeNode("Extanded Events Sessions", 1, 1);
                                {
                                    int i = 0;
                                    if (_I_DBModel != null)
                                        foreach (var element in _I_DBModel.Model)
                                        {
                                            if (element.Type == "SqlEventSession")
                                            {
                                                i++;
                                                var elementNode = new TreeNode(element.Name, 0, 0);
                                                SqlErrorMessageLevel.Nodes.Add(elementNode);
                                            }
                                        }
                                    SqlErrorMessageLevel.Text += string.Format(" ({0})", arg0: i.ToString());
                                }
                                ServerManagmentLevel.Nodes.Add(SqlErrorMessageLevel);
                            }
                            ///////////////////
                            ServerLevel.Nodes.Add(ServerManagmentLevel);
                        }

                    }
                    ModelLevel.Nodes.Add(ServerLevel);
                }

                //Pre Post Scripts
                {
                    var PrePostLevel = new TreeNode("Pre\\Post Scripts", 18, 18);
                    ///////////////////
                    if ((!PostScript) && (!PreScript))
                        PrePostLevel.Nodes.Add("no pre\\post scripts");
                    if (PreScript)
                        PrePostLevel.Nodes.Add(new TreeNode("predeploy.sql", 18, 18));
                    if (PostScript)
                        PrePostLevel.Nodes.Add(new TreeNode("postdeploy.sql", 18, 18));
                    //////////////////
                    ModelLevel.Nodes.Add(PrePostLevel);
                }

                //refactor Log
                {
                    var refactorLevel = new TreeNode("refactor Log", 1,1);
                    ///////////////////
                    if (refactorlog)
                        refactorLevel.Nodes.Add("reflector logs");
                    //////////////////
                    ModelLevel.Nodes.Add(refactorLevel);
                }

                treeView1.Nodes.Add(ModelLevel);
                if (IsDataIncludes)
                {
                    var DatamodelLevel = new TreeNode(string.Format("{0} - ({1})", DatabaseName, DatabaseVersion), 0, 0);
                    foreach (string oo in DataTableIncludes)
                    {
                        DatamodelLevel.Nodes.Add(oo);
                    }
                    DatamodelLevel.Expand();
                    treeView2.Nodes.Add(DatamodelLevel);
                }
                else
                {
                    var DatamodelLevel = new TreeNode(string.Format("{0} - ({1})", DatabaseName, DatabaseVersion), 0, 0);
                    DatamodelLevel.Nodes.Add("no data is includes");
                    DatamodelLevel.Expand();
                    treeView2.Nodes.Add(DatamodelLevel);
                }
            }
        }

        void ExtractModelDetailes(Stream XMLDacMetadata, out string DatabaseName, out string DatabaseVersion, out string DatabaseDescription)
        {
            DatabaseName = "";
            DatabaseVersion = "";
            DatabaseDescription = "";
            using (XmlReader xmlReader = XmlReader.Create(XMLDacMetadata))
            {
                var serializer = new XmlSerializer(typeof(DacType));
                var deserializedDacMetadata = serializer.Deserialize(xmlReader) as DacType;
                DatabaseName = deserializedDacMetadata.Name;
                DatabaseVersion = deserializedDacMetadata.Version;
                DatabaseDescription = deserializedDacMetadata.Description;

            }
            

        }

        //Close
        private void toolStripButton2_Click(object sender, EventArgs e)
        {
            foreach (TreeNode node in treeView1.Nodes)
            {
                treeView1.Nodes.Remove(node);
            }

            foreach (TreeNode node in treeView2.Nodes)
            {
                treeView2.Nodes.Remove(node);
            }

            FilePath = "";  

            toolStripButton1.Enabled = true;

        }

        private void toolStripButton5_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void treeView1_DoubleClick(object sender, EventArgs e)
        {
            if (treeView1.SelectedNode == null)
                return;

            //if this is object name
            if (treeView1.SelectedNode.Text.Contains('['))
            {
                OpenObjectDefinition(treeView1.SelectedNode.Text);
            }

            if (treeView1.SelectedNode.Text == "postdeploy.sql")
            {
                Open_TsqlEditor(treeView1.SelectedNode.Text, _PostScriptContent);
                return;
            }
            if (treeView1.SelectedNode.Text == "predeploy.sql")
            {
                Open_TsqlEditor(treeView1.SelectedNode.Text, _PreScriptContent);
                return;
            }


        }

        private void OpenObjectDefinition(string name)
        {
            foreach (var element in _I_DBModel.Model)
            {
                if (element.Name == name)
                {
                    switch (element.Type)
                    {
                        case "SqlTable":
                            {
                                string Body = "";
                                string header = "CREATE TABLE " + element.Name + Environment.NewLine;
                                string ColumnList = "";
                                foreach (var itm in element.Items)
                                {
                                    if (itm.GetType() == typeof(DataSchemaModelElementRelationship))
                                    {
                                        var Relationship = (DataSchemaModelElementRelationship)itm;
                                        if (Relationship.Name == "Columns")
                                        {
                                            foreach (var column in Relationship.Entry)
                                            {
                                                string columnname = column.Element.Name.Remove(0, column.Element.Name.LastIndexOf('[')+1).Replace("]", "") + " ";
                                                string columnDef = "";
                                                foreach (var TypeEntry in column.Element.Relationship.Entry)
                                                {
                                                    if (TypeEntry.Element.Type == "SqlTypeSpecifier")
                                                    {
                                                        //Type
                                                        foreach (var r in TypeEntry.Element.Relationship)
                                                        {
                                                            columnDef = r.Name;
                                                        }

                                                        //Lenth
                                                        if (TypeEntry.Element.Property != null)
                                                        {
                                                            foreach (var r in TypeEntry.Element.Property)
                                                            {
                                                                if (r.Name == "Length")
                                                                    columnDef += string.Format("({0}) ", r.Value);
                                                            }
                                                        }
                                                    }
                                                    if (column.Element.Property != null)
                                                    {
                                                        foreach (var r in column.Element.Property)
                                                        {
                                                            if ( (r.Name == "IsNullable") &&(r.Value=="True"))
                                                            {
                                                                columnDef += "Nullable ";
                                                            }

                                                            if ((r.Name == "IsIdentity") && (r.Value == "True"))
                                                            {
                                                                columnDef += "Identity ";
                                                            }
                                                        }
                                                    }
                                                }
                                                ColumnList += columnname + columnDef;

                                            }
                                        }
                                    }
                                }
                                string Script = header + "\nAS\n" + ColumnList;
                                Open_TsqlEditor(element.Name, Script);
                            }break;

                        case "SqlProcedure":
                            {
                                string Body = "";
                                string header = "CREATE PROCEDURE " + element.Name + Environment.NewLine;
                                string ParmsList = "";
                                foreach (var itm in element.Items)
                                {
                                    if ((itm.GetType() == typeof(DataSchemaModelElementProperty)))
                                    {
                                        var t = (DataSchemaModelElementProperty)itm;
                                        if (t.Name == "BodyScript")
                                            Body = t.Value;
                                    }
                                    if ((itm.GetType() == typeof(DataSchemaModelElementRelationship)))
                                    {
                                        var t = (DataSchemaModelElementRelationship)itm;
                                        if (t.Name == "Parameters")
                                        {
                                            foreach (var prams in t.Entry)
                                            {
                                                var parm_Title = prams.Element.Name.Remove(0, prams.Element.Name.LastIndexOf('[') + 1).Replace("]", "") + " ";
                                                foreach (var type in prams.Element.Relationship.Entry)
                                                {
                                                    //Getting the parameter type
                                                    foreach (var _itype in type.Element.Relationship)
                                                    {
                                                        if (_itype.Name != "Type")
                                                            parm_Title += _itype.Name;
                                                        else
                                                            parm_Title += _itype.Entry.References.Name;

                                                    }

                                                    //Getting the parameter Lenth
                                                    if (type.Element.Property != null)
                                                    {
                                                        foreach (var _itype in type.Element.Property)
                                                        {
                                                            if (_itype.Name == "Length")
                                                                parm_Title += string.Format("({0})", _itype.Value);
                                                        }
                                                    }
                                                    header += parm_Title + "," + Environment.NewLine;
                                                }

                                            }
                                        }
                                    }
                                }
                                string Script = header + "\nAS\n" + Body;
                                Open_TsqlEditor(element.Name, Script);
                                
                            }break;
                    }
                }
            }
        }

        private void OnUnlodedObjects(object sender, ControlEventArgs e)
        {
            if (sender.GetType() == typeof(TabPage))
            {
                codetabs.TabPages.Remove((TabPage)sender);
            }
        }

        private void Open_TsqlEditor(string title, string content)
        {
            var ooo = new TabPage();
            ooo.Text = title;
            var t = new tsqlEditor(content);
            t.Dock = DockStyle.Fill;
            ooo.Controls.Add(t);
            ooo.ControlRemoved += OnUnlodedObjects;
            codetabs.TabPages.Add(ooo);
        }
    }
}
